/*******************************************************************************
Replication Materials for Blau, Kahn, Brummund, Cook, and Larson-Koester "Is 
There Still Son Preference in the United States?"

Table 6

Date Modified: 10/22/2019

*******************************************************************************/




*-------------------------------------------------------------------------------
                                  *LOAD DATA
*-------------------------------------------------------------------------------
use "$acs\acs_data",clear


*-------------------------------------------------------------------------------
                                *RESTRICT SAMPLE
*-------------------------------------------------------------------------------
keep if nonrkids == 0 & nonusbirth == 0 & sfrelate == 0 & ///
	father_sample != 1 & multi_sample != 1 & mult == 0 & female == 1 & ///
	oldc <=12 & inrange(age,18,40)==1 & foster_hh == 0 & nchild18>=1
drop if citizen == 1 | marst == 5

*-------------------------------------------------------------------------------
                                *GENERATE NEW VARS
*-------------------------------------------------------------------------------
forv jj = 1/4 {
	g c_male`jj' = (c_female`jj' == 0)
}

*-------------------------------------------------------------------------------
                              *GENERATE SAMPLES
*-------------------------------------------------------------------------------
// Second Child, First Child Boy
gen sample1 = chld1 == 0 & gt1chld == 1

// Second Child, First Child Girl
gen sample2 = chld1 == 1 & gt1chld == 1

// Third Child, First Two Children Boys
gen sample3 = chld2 == 0 & gt2chld == 1

// Third Child, First Two Children Girls
gen sample4 = chld2 == 1 & gt2chld == 1

// Third Child, First Two Children Mix 
gen sample5 = chld2 == 2 & gt2chld == 1 


*-------------------------------------------------------------------------------
                       *SET UP EXCEL FILE THAT WILL STORE ESTIMATES
*-------------------------------------------------------------------------------
putexcel set "$toutput\Table_6", replace

// TITLE:
putexcel A1 = "Table 6: Boy/Girl Ratio, Second and Third Children" 

// COLUMNS:
putexcel A4 = "A. Second Child" 
putexcel A5 = "First Child Boy" 
putexcel A6 = "Sex Ratio" 
putexcel A7 = "95% Conf. Int." 
putexcel A8 = "Sample Size" 

putexcel A10 = "First Child Girl" 
putexcel A11 = "Sex Ratio" 
putexcel A12 = "95% Conf. Int." 
putexcel A13 = "Sample Size" 

putexcel A14 = "B. Third Child" 
putexcel A15 = "First Two Children Boys" 
putexcel A16 = "Sex Ratio" 
putexcel A17 = "95% Conf. Int." 
putexcel A18 = "Sample Size" 

putexcel A20 = "First Two Children Girls" 
putexcel A21 = "Sex Ratio" 
putexcel A22 = "95% Conf. Int." 
putexcel A23 = "Sample Size" 

putexcel A25 = "First Two Children Mix" 
putexcel A26 = "Sex Ratio" 
putexcel A27 = "95% Conf. Int." 
putexcel A28 = "Sample Size" 


// ROWS:
putexcel B2 = "All Married Women" 
putexcel D2 = "Women in First Marriage" 
putexcel F2 = "All Women" 
putexcel B3= "Natives" 
putexcel D3= "Natives" 
putexcel F3= "Natives" 
putexcel C3= "Immigrants" 
putexcel E3= "Immigrants" 
putexcel G3= "Immigrants"


*-------------------------------------------------------------------------------
                                  *SUMMARY STATS
*-------------------------------------------------------------------------------
gen sample = 0

// All Married Women, Natives
local j = 6
local k = 7
local l = 8
forvalues i = 1/5 {

	// Define sample
	replace sample = 0
	replace sample = 1 if marst == 1 & native == 1 & sample`i' == 1
	
	// Dependent variable if statement (second child or third child variable)
	if `i' < 3 {
	local var "c_male2"
	}
	else {
	local var "c_male3"
	}
	
	// calculate the sex ratio, CI and obs
	mean `var' if (sample == 1) [aw = hhwtnorm]
	nlcom _b[`var']/(1-_b[`var']), post		
	local SR: display %9.3fc _b[_nl_1]
	local CI_L: display %9.3fc _b[_nl_1] - invnormal(.975)*_se[_nl_1]
	local CI_L = ltrim("`CI_L'")
	local CI_U : display %9.3fc _b[_nl_1] + invnormal(.975)*_se[_nl_1]
	local CI_U = ltrim("`CI_U'")	
	count if (sample == 1)
	local N : display %10.3gc r(N)
	
	// write into excel
	putexcel B`j' = `SR'
	putexcel B`k' = "[`CI_L', `CI_U']"
	putexcel B`l' = "`N'"
	
	// iterate to write the next set of rows
	local j = `j' + 5
	local k = `k' + 5
	local l = `l' + 5
		
}

// All Married Women, Immigrants
local j = 6
local k = 7
local l = 8
forvalues i = 1/5 {


	replace sample = 0
	replace sample = 1 if marst == 1 & native == 0 & sample`i' == 1
	
	if `i' < 3 {
	local var "c_male2"
	}
	else {
	local var "c_male3"
	}
	
	mean `var' if (sample == 1) [aw = hhwtnorm]
	nlcom _b[`var']/(1-_b[`var']), post		
	local SR: display %9.3fc _b[_nl_1]
	local CI_L: display %9.3fc _b[_nl_1] - invnormal(.975)*_se[_nl_1]
	local CI_L = ltrim("`CI_L'")
	local CI_U : display %9.3fc _b[_nl_1] + invnormal(.975)*_se[_nl_1]
	local CI_U = ltrim("`CI_U'")	
	count if (sample == 1)
	local N: display %10.3gc r(N)
	
	putexcel C`j' = `SR'
	putexcel C`k' = "[`CI_L', `CI_U']"
	putexcel C`l' = "`N'"
	
	local j = `j' + 5
	local k = `k' + 5
	local l = `l' + 5
	
}

// Women in First Marriage, Natives
local j = 6
local k = 7
local l = 8
forvalues i = 1/5 {

	replace sample = 0
	replace sample = 1 if marst == 1 & bothfirmarr == 1 & native == 1 & sample`i' == 1
	
	if `i' < 3 {
	local var "c_male2"
	}
	else {
	local var "c_male3"
	}	

	mean `var' if (sample == 1) [aw = hhwtnorm]
	nlcom _b[`var']/(1-_b[`var']), post		
	local SR: display %9.3fc _b[_nl_1]
	local CI_L: display %9.3fc _b[_nl_1] - invnormal(.975)*_se[_nl_1]
	local CI_L = ltrim("`CI_L'")
	local CI_U : display %9.3fc _b[_nl_1] + invnormal(.975)*_se[_nl_1]
	local CI_U = ltrim("`CI_U'")	
	count if (sample == 1)
	local N: display %10.3gc r(N)
	
	putexcel D`j' = `SR'
	putexcel D`k' = "[`CI_L', `CI_U']"
	putexcel D`l' = "`N'"
	
	local j = `j' + 5
	local k = `k' + 5
	local l = `l' + 5	
	
}

// Women in First Marriage, Immigrants
local j = 6
local k = 7
local l = 8
forvalues i = 1/5 {

	replace sample = 0
	replace sample = 1 if marst == 1 & bothfirmarr == 1 & native == 0 & sample`i' == 1
	
	if `i' < 3 {
	local var "c_male2"
	}
	else {
	local var "c_male3"
	}	

	mean `var' if (sample == 1) [aw = hhwtnorm]
	nlcom _b[`var']/(1-_b[`var']), post		
	local SR: display %9.3fc _b[_nl_1]
	local CI_L: display %9.3fc _b[_nl_1] - invnormal(.975)*_se[_nl_1]
	local CI_L = ltrim("`CI_L'")
	local CI_U : display %9.3fc _b[_nl_1] + invnormal(.975)*_se[_nl_1]
	local CI_U = ltrim("`CI_U'")	
	count if (sample == 1)
	local N: display %10.3gc r(N)
	
	putexcel E`j' = `SR'
	putexcel E`k' = "[`CI_L', `CI_U']"
	putexcel E`l' = "`N'"
	
	local j = `j' + 5
	local k = `k' + 5
	local l = `l' + 5		
	
}

// All Women, Natives
local j = 6
local k = 7
local l = 8
forvalues i = 1/5 {

	replace sample = 0
	replace sample = 1 if native == 1 & sample`i' == 1
	
	if `i' < 3 {
	local var "c_male2"
	}
	else {
	local var "c_male3"
	}	

	mean `var' if (sample == 1) [aw = hhwtnorm]
	nlcom _b[`var']/(1-_b[`var']), post		
	local SR: display %9.3fc _b[_nl_1]
	local CI_L: display %9.3fc _b[_nl_1] - invnormal(.975)*_se[_nl_1]
	local CI_L = ltrim("`CI_L'")
	local CI_U : display %9.3fc _b[_nl_1] + invnormal(.975)*_se[_nl_1]
	local CI_U = ltrim("`CI_U'")	
	count if (sample == 1)
	local N: display %10.3gc r(N)
	
	putexcel F`j' = `SR'
	putexcel F`k' = "[`CI_L', `CI_U']"
	putexcel F`l' = "`N'"
	
	local j = `j' + 5
	local k = `k' + 5
	local l = `l' + 5		
	
}

// All Women, Immigrants
local j = 6
local k = 7
local l = 8
forvalues i = 1/5 {

	replace sample = 0
	replace sample = 1 if native == 0 & sample`i' == 1
	
	if `i' < 3 {
	local var "c_male2"
	}
	else {
	local var "c_male3"
	}	

	mean `var' if (sample == 1) [aw = hhwtnorm]
	nlcom _b[`var']/(1-_b[`var']), post		
	local SR: display %9.3fc _b[_nl_1]
	local CI_L: display %9.3fc _b[_nl_1] - invnormal(.975)*_se[_nl_1]
	local CI_L = ltrim("`CI_L'")
	local CI_U : display %9.3fc _b[_nl_1] + invnormal(.975)*_se[_nl_1]
	local CI_U = ltrim("`CI_U'")	
	count if (sample == 1)
	local N: display %10.3gc r(N)
	
	putexcel G`j' = `SR'
	putexcel G`k' = "[`CI_L', `CI_U']"
	putexcel G`l' = "`N'"
	
	local j = `j' + 5
	local k = `k' + 5
	local l = `l' + 5	
	
}
